CREATE SCHEMA GESTION_PROYECTO;

CREATE TABLE GESTION_PROYECTO.DESPACHO ( 
	codigo_despacho INTEGER NOT NULL,
	nro_telefono VARCHAR(25) NOT NULL, 
	CONSTRAINT pk_despacho PRIMARY KEY (codigo_despacho)
);

CREATE TABLE GESTION_PROYECTO.EDITORIAL ( 
	codigo_editorial INTEGER NOT NULL,
	descripcion VARCHAR(50) NOT NULL,
	CONSTRAINT pk_editorial PRIMARY KEY (codigo_editorial)
);

CREATE TABLE GESTION_PROYECTO.PROFESOR ( 
	codigo_profesor SERIAL NOT NULL,
	nombres VARCHAR(50) NOT NULL,
	apellidos VARCHAR(50) NOT NULL,
	es_doctor BOOLEAN NOT NULL,
	codigo_despacho INTEGER NOT NULL,
	CONSTRAINT pk_profesor PRIMARY KEY (codigo_profesor),
	CONSTRAINT ak_nomb_apell UNIQUE (nombres, apellidos),
    CONSTRAINT fk_prof_despacho FOREIGN KEY 
        (codigo_despacho) REFERENCES 
		GESTION_PROYECTO.DESPACHO 
		(codigo_despacho) ON UPDATE CASCADE 
        ON DELETE NO ACTION
);

CREATE TABLE GESTION_PROYECTO.TIPO_FINANCIAMIENTO ( 
	codigo_financiamiento INTEGER NOT NULL,
	descripcion VARCHAR(50) NOT NULL,
	CONSTRAINT pk_tipo_finan PRIMARY KEY ( codigo_financiamiento )
);

CREATE TABLE GESTION_PROYECTO.PROYECTO ( 
	codigo_referencia INTEGER NOT NULL,
	acronimo VARCHAR(50) NOT NULL,
	fecha_inicio DATE NOT NULL,
	fecha_fin DATE NOT NULL,
	nombre VARCHAR(50) NOT NULL,
	presupuesto FLOAT NOT NULL,
	codigo_financiamiento INTEGER NOT NULL, 
	codigo_supervisor INTEGER NOT NULL,
	CONSTRAINT pk_proyecto PRIMARY KEY (codigo_referencia), 
	CONSTRAINT fk_proy_tipofin FOREIGN KEY 
	    (codigo_financiamiento ) REFERENCES 
		GESTION_PROYECTO.TIPO_FINANCIAMIENTO 
		(codigo_financiamiento) 
		ON UPDATE CASCADE 
        ON DELETE NO ACTION,
	CONSTRAINT fk_proy_superv FOREIGN KEY
	    (codigo_supervisor) REFERENCES 
		GESTION_PROYECTO.PROFESOR (codigo_profesor) 
	    ON UPDATE NO ACTION 
        ON DELETE NO ACTION,
    CONSTRAINT chk_periodo_ss CHECK (fecha_inicio < fecha_fin)
);

CREATE TABLE GESTION_PROYECTO.ES_INVESTIGADOR ( 
	codigo_profesor INTEGER NOT NULL,
	codigo_referencia INTEGER NOT NULL,
	fecha_inicio DATE NOT NULL,
	fecha_fin DATE,
	CONSTRAINT pk_es_investig 
	   PRIMARY KEY ( codigo_profesor, codigo_referencia, fecha_inicio),
	CONSTRAINT fk_es_inv_prof FOREIGN KEY (codigo_profesor)
	   REFERENCES 
	   GESTION_PROYECTO.PROFESOR (codigo_profesor) 
       ON UPDATE CASCADE ON DELETE NO ACTION,
	CONSTRAINT fk_es_inv_proy FOREIGN KEY (codigo_referencia)
	   REFERENCES 
	   GESTION_PROYECTO.PROYECTO (codigo_referencia)
       ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT chk_es_inves_periodo CHECK (fecha_fin IS NULL OR
              (fecha_fin IS NOT NULL AND fecha_inicio <= fecha_fin)) 
);

CREATE TABLE GESTION_PROYECTO.LINEA_INVESTIGACION ( 
	codigo_linea INTEGER NOT NULL,
	nombre VARCHAR(50) NOT NULL,
	descripcion VARCHAR(50) NOT NULL,
	CONSTRAINT pk_linea_invest PRIMARY KEY (codigo_linea),
	CONSTRAINT ak_linea_invest UNIQUE(nombre)
);

CREATE DOMAIN GESTION_PROYECTO.TipoPublicacion AS CHAR(1) NOT NULL
              CHECK (VALUE IN ('R','C'));

CREATE TABLE GESTION_PROYECTO.PUBLICACION ( 
	codigo_referencia INTEGER NOT NULL,
	numero_publicacion INTEGER NOT NULL,
	titulo VARCHAR(50) NOT NULL,
	codigo_editorial INTEGER, 
    tipo_publicacion GESTION_PROYECTO.TipoPublicacion NOT NULL,
	CONSTRAINT pk_publicac PRIMARY KEY 
	  (codigo_referencia, numero_publicacion), 
	CONSTRAINT fk_public_proy FOREIGN KEY (codigo_referencia)
	   REFERENCES 
	   GESTION_PROYECTO.PROYECTO (codigo_referencia)
   	   ON UPDATE CASCADE ON DELETE NO ACTION,
     CONSTRAINT fk_public_editor FOREIGN KEY (codigo_editorial)
       REFERENCES 
       GESTION_PROYECTO.EDITORIAL (codigo_editorial)
       ON UPDATE CASCADE ON DELETE NO ACTION,
     CONSTRAINT chk_pub_rev_con_edit 
         CHECK ((tipo_publicacion = 'R' AND 
                 codigo_editorial IS NOT NULL) OR
                 tipo_publicacion = 'C')
);

CREATE TABLE GESTION_PROYECTO.PUBLICACION_CONGRESO ( 
	codigo_referencia INTEGER NOT NULL,
	numero_publicacion INTEGER NOT NULL,
	fecha_inicio DATE NOT NULL,
	fecha_fin DATE NOT NULL,
	nombre_congreso VARCHAR(50) NOT NULL,
	CONSTRAINT pk_pub_cong PRIMARY KEY
	   (codigo_referencia, numero_publicacion), 
	CONSTRAINT fk_pub_cong_publica 
	   FOREIGN KEY (codigo_referencia, numero_publicacion)
       REFERENCES GESTION_PROYECTO.PUBLICACION 
      (codigo_referencia, numero_publicacion)
   	   ON UPDATE CASCADE ON DELETE NO ACTION,
   	CONSTRAINT chk_pub_cong_periodo CHECK (fecha_inicio < fecha_fin)
);

CREATE TABLE GESTION_PROYECTO.PUBLICACION_REVISTA ( 
    codigo_referencia INTEGER NOT NULL,
	numero_publicacion INTEGER NOT NULL,
	nombre_revista varchar(50) NOT NULL,
	volumen INTEGER NOT NULL,
	numero INTEGER NOT NULL,
	pagina_inicio SMALLINT NOT NULL,
	pagina_fin SMALLINT NOT NULL,
	CONSTRAINT pk_pub_rev PRIMARY KEY 
	   (codigo_referencia, numero_publicacion),
    CONSTRAINT fk_pub_rev_publica FOREIGN KEY
       (codigo_referencia, numero_publicacion)
       REFERENCES GESTION_PROYECTO.PUBLICACION 
       (codigo_referencia, numero_publicacion)
   	   ON UPDATE CASCADE ON DELETE NO ACTION,
   	CONSTRAINT chk_pub_rev_paginas CHECK (pagina_inicio <= pagina_fin)
);

CREATE TABLE GESTION_PROYECTO.ABARCA ( 
	codigo_referencia INTEGER NOT NULL,
	numero_publicacion INTEGER NOT NULL,
	codigo_linea INTEGER NOT NULL,
	CONSTRAINT pk_abarca 
	    PRIMARY KEY(codigo_referencia, 
	    numero_publicacion, codigo_linea ),
	CONSTRAINT fk_abarca_pub FOREIGN KEY(codigo_referencia, 
	    numero_publicacion) 
	    REFERENCES GESTION_PROYECTO.PUBLICACION 
	    (codigo_referencia, numero_publicacion)
        ON UPDATE CASCADE ON DELETE NO ACTION, 
 	CONSTRAINT fk_abarca_lin_inv FOREIGN KEY (codigo_linea) 
        REFERENCES GESTION_PROYECTO.LINEA_INVESTIGACION 
        (codigo_linea )
        ON UPDATE CASCADE ON DELETE NO ACTION
);

CREATE TABLE GESTION_PROYECTO.ESCRIBE ( 
	codigo_referencia INTEGER NOT NULL,
	numero_publicacion INTEGER NOT NULL,
	codigo_linea INTEGER NOT NULL,
	codigo_profesor INTEGER NOT NULL,
	CONSTRAINT pk_escribe PRIMARY KEY 
	   (codigo_referencia, numero_publicacion, codigo_linea, 
		codigo_profesor),
	CONSTRAINT fk_escribe_pub FOREIGN KEY 
	   (codigo_referencia, numero_publicacion) 
       REFERENCES GESTION_PROYECTO.PUBLICACION 
       (codigo_referencia, numero_publicacion)
       ON UPDATE CASCADE ON DELETE NO ACTION, 
	CONSTRAINT fk_escribe_prof FOREIGN KEY (codigo_profesor) 
	   REFERENCES GESTION_PROYECTO.PROFESOR (codigo_profesor)
       ON UPDATE CASCADE ON DELETE NO ACTION, 
	CONSTRAINT fk_escribe_lin_inv FOREIGN KEY (codigo_linea) 
       REFERENCES GESTION_PROYECTO.LINEA_INVESTIGACION 
       (codigo_linea)
       ON UPDATE CASCADE ON DELETE NO ACTION
);





CREATE OR REPLACE FUNCTION verificarProyecto() 
  RETURNS TRIGGER AS $trigger_ver_proyecto$
  DECLARE
    esDoctor BOOLEAN;
  BEGIN
    IF (TG_OP = 'INSERT') THEN
      SELECT es_doctor INTO esDoctor FROM GESTION_PROYECTO.PROFESOR
       WHERE codigo_profesor = NEW.codigo_supervisor;
       IF NOT esDoctor THEN
         RETURN NEW;
       ELSE
         RAISE EXCEPTION 'Codigo supervisor % no es doctor', 
            NEW.codigo_supervisor 
            USING HINT = 'Supervisor no es doctor';
       END IF;
    ELSEIF (TG_OP = 'UPDATE') THEN
       IF NEW.codigo_supervisor <> OLD.codigo_supervisor THEN
         RAISE EXCEPTION 'No puede cambiar el supervisor'
            USING HINT = 'Supervisor no se cambia';
       ELSEIF NEW.presupuesto <> OLD.presupuesto THEN
         RAISE EXCEPTION 'No puede cambiar el presupuesto'
            USING HINT = 'Presupuesto no se cambia';
       ELSE
         RETURN NEW;
       END IF;
    END IF;   
  END;
$trigger_ver_proyecto$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_ver_proyecto
  BEFORE INSERT OR UPDATE OF codigo_supervisor, presupuesto ON 
                             GESTION_PROYECTO.PROYECTO
  FOR EACH ROW
  EXECUTE PROCEDURE verificarProyecto();

CREATE OR REPLACE FUNCTION verificaLineasEscribeProf() 
  RETURNS TRIGGER AS $trigger_ver_lin_escribe_prof$
  DECLARE
    conteo SMALLINT;
  BEGIN
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
      SELECT COUNT(1) INTO conteo
        FROM GESTION_PROYECTO.PROYECTO AS PR NATURAL JOIN               
              GESTION_PROYECTO.ABARCA AS AB NATURAL JOIN
              GESTION_PROYECTO.ES_INVESTIGADOR EI
       WHERE EI.codigo_profesor = NEW.codigo_profesor AND
             AB.codigo_linea = NEW.codigo_linea AND
             PR.codigo_referencia = NEW.codigo_referencia;
       IF conteo = 0 THEN
          RAISE EXCEPTION 
           'Profesor no escribe en linea fuera de proyecto'
            USING HINT = 
           'Publicacion con linea fuera de proyecto';
       ELSE
          RETURN NEW;
       END IF;
    END IF;
  END;
$trigger_ver_lin_escribe_prof$ LANGUAGE plpgsql; 
  
CREATE TRIGGER trigger_ver_lin_escribe_prof
  BEFORE INSERT OR UPDATE ON GESTION_PROYECTO.ESCRIBE
  FOR EACH ROW
  EXECUTE PROCEDURE verificaLineasEscribeProf();
  
CREATE OR REPLACE FUNCTION verificaPeriodoInvestigador() 
  RETURNS TRIGGER AS $trigger_periodo_investigador$
  DECLARE
    fecIniProy DATE;
    fecFinProy DATE;
  BEGIN
    SELECT fecha_inicio, fecha_fin INTO fecIniProy, fecFinProy
      FROM GESTION_PROYECTO.PROYECTO 
     WHERE codigo_referencia = NEW.codigo_referencia;
     
    IF NEW.fecha_inicio < fecIniProy THEN
      RAISE EXCEPTION 
           'Fecha inicio del investigador fuera de rango del proyecto'
            USING HINT = 
           'Fecha inicio investigador invalida';
    END IF;
    IF NEW.fecha_fin IS NOT NULL AND
       NEW.fecha_inicio > fecFinProy THEN
      RAISE EXCEPTION 
           'Fecha final del investigador fuera de rango del proyecto'
            USING HINT = 
           'Fecha final investigador invalida';
    END IF;
    RETURN NEW;
  END;
$trigger_periodo_investigador$ LANGUAGE plpgsql; 
  
  
CREATE TRIGGER trigger_periodo_investigador
  BEFORE INSERT OR UPDATE OF fecha_inicio, fecha_fin
  ON GESTION_PROYECTO.ES_INVESTIGADOR
  FOR EACH ROW
  EXECUTE PROCEDURE verificaPeriodoInvestigador();  
